<%@page import="org.postgresql.Driver"%>
<html>
<head>
<script type="text/javascript">
	function Insert() {
		document.getElementById("coursenumber").required = true;
		document.getElementById("maxunits").required = true;
		document.getElementById("ACTION").value = "insert";
	}
	function Update(row) {
		document.getElementById("coursenumber"+row).required = true;
		document.getElementById("maxunits"+row).required = true;
		document.getElementById("ACTION").value = "update";
		document.getElementById("PARAMETER").value = row;
	}
	function Delete(row) {
		document.getElementById("ACTION").value = "delete";
		document.getElementById("PARAMETER").value = row;
	}
</script>
</head>
<body>
	<%@ page language="java" import="java.sql.*"%>
	<%
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/cse132b",
					"cse132blogin", "1234567890");
			String action = request.getParameter("ACTION");
			// Check if an insertion is requested
			if (action != null && action.equals("insert")) {
				int NewID = 0;
				
				conn.setAutoCommit(false);
						
				PreparedStatement pstmt;
				if (request.getParameter("minunits").equals("")) {
					pstmt = conn.prepareStatement("INSERT INTO courses (department_abbreviation,max_units,course_number,grade_type,requires_lab) VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
				} else {
					pstmt = conn.prepareStatement("INSERT INTO courses (department_abbreviation,max_units,course_number,grade_type,requires_lab,min_units) VALUES (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
				}
				pstmt.setString(1,
						request.getParameter("departmentabbreviation"));
				pstmt.setInt(2,
						Integer.parseInt(request.getParameter("maxunits")));
				pstmt.setString(3, request.getParameter("coursenumber"));
				pstmt.setString(4, request.getParameter("gradetype"));
				pstmt.setBoolean(
						5,
						request.getParameter("requireslab") != null ? request
								.getParameter("requireslab").equals(
										"required") : false);
				if (!request.getParameter("minunits").equals("")) {
					pstmt.setInt(6, Integer.parseInt(request
							.getParameter("minunits")));
				}
				int rowCount = pstmt.executeUpdate();
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					NewID = rs.getInt(1);
				}
				rs.close();
				pstmt.close();
				
				
				String arrCategory[] = request.getParameterValues("sltCategory");
				for (int i = 0; i < arrCategory.length; i++) {
					PreparedStatement statement = conn
							.prepareStatement("INSERT INTO course_category (Category_id, course_id) VALUES (?,?)");
					statement.setInt(1, Integer.parseInt(arrCategory[i]));
					statement.setInt(2, NewID);
					statement.executeUpdate();
					statement.close();
				}
				

				
				conn.commit();
				conn.setAutoCommit(true);
				// Commit transaction
				out.println("Successfully made new course.<br/>");
			} else if (action != null && action.equals("update")) {
				PreparedStatement pstmt;
				int editId = Integer.parseInt(request
						.getParameter("PARAMETER"));
				pstmt = conn
						.prepareStatement("UPDATE courses SET department_abbreviation = ?,min_units = ?,max_units = ?,course_number = ?,grade_type = ?,requires_lab = ? WHERE course_id = ?");
				pstmt.setString(
						1,
						request.getParameter("departmentabbreviation"
								+ editId));
				if (request.getParameter("minunits" + editId).equals("")) {
					pstmt.setNull(2, java.sql.Types.INTEGER);
				} else {
					pstmt.setInt(2, Integer.parseInt(request
							.getParameter("minunits" + editId)));
				}
				pstmt.setInt(
						3,
						Integer.parseInt(request.getParameter("maxunits"
								+ editId)));
				pstmt.setString(4,
						request.getParameter("coursenumber" + editId));
				pstmt.setString(5,
						request.getParameter("gradetype" + editId));
				pstmt.setBoolean(
						6,
						request.getParameter("requireslab" + editId) != null ? request
								.getParameter("requireslab" + editId)
								.equals("required") : false);
				pstmt.setInt(7, editId);
				int rowCount = pstmt.executeUpdate();

				// Commit transaction
				out.println("Updated course.<br/>");
			} else if (action != null && action.equals("delete")) {
				int editId = Integer.parseInt(request.getParameter("PARAMETER"));
				PreparedStatement pstmt;
				pstmt = conn.prepareStatement("DELETE FROM courses WHERE course_id = ?");
				pstmt.setInt(1, editId);
				pstmt.executeUpdate();
			}

			Statement stmt = conn.createStatement();
			ResultSet rsDepartments = stmt
					.executeQuery("SELECT * FROM departments");
	%>
	<form action="courses.jsp" method="post">

		<table border="1">
			<tr>
				<td valign="top" width="40%">
					<%-- -------- Include menu HTML code -------- --%> <jsp:include
						page="/menu.html" />
				</td>
				<td>
					<table border="0">
						<tr>
							<td valign="top">Courses Entry Page</td>
						</tr>
						<tr>
							<input type="hidden" id="ACTION" name="ACTION" value="None">
							<input type="hidden" id="PARAMETER" name="PARAMETER" value="">
							<table border="1">
								<tr>
									<th>Department*</th>
									<th>Course Number*</th>
									<th>Min Units</th>
									<th>Max Units*</th>
									<th>Grade Type*</th>
									<th>Requires Lab*</th>
								</tr>
								<tr>

									<td><select name="departmentabbreviation">
											<%
												while (rsDepartments.next()) {
											%>
											<option value="<%=rsDepartments.getString("abbreviation")%>"><%=rsDepartments.getString("abbreviation")%></option>
											<%
												}
											%>
									</select></td>
									<td><input value="" id="coursenumber" name="coursenumber" size="10"></td>
									<td><input value="" id="minunits" name="minunits" size="10"></td>
									<td><input value="" id="maxunits" name="maxunits" size="15"></td>
									<td><select name="gradetype">
											<option value="gr">Graded</option>
											<option value="gs">Graded or
												Satisfactory/Unsatisfactory</option>
											<option value="su">Satisfactory/Unsatisfactory</option>
									</select></td>
									<td><input type="checkbox" name="requireslab"
										value="required">Requires Lab</td>
									<td><select name="sltCategory" id="sltCategory" multiple>
									<%
										stmt = conn.createStatement();
										ResultSet rsCategory = stmt.executeQuery("SELECT * FROM Category");
										while (rsCategory.next()) {
									%>
									<option value="<%=rsCategory.getInt("Category_id")%>"><%=rsCategory.getString("name")%></option>
									<%
										}
										rsCategory.close();
										stmt.close();
									%>
							</select></td>
									<td><input type="submit" onclick="Insert()"></input></td>
								</tr>
								<%
									Statement existingCourses = conn.createStatement();
										ResultSet rsExistingCourses = existingCourses
												.executeQuery("SELECT * FROM courses");
										while (rsExistingCourses.next()) {
								%>
								<tr>

									<td><select
										id="departmentabbreviation<%=rsExistingCourses.getInt("course_id")%>"
										name="departmentabbreviation<%=rsExistingCourses.getInt("course_id")%>">
											<%
												rsDepartments = conn.createStatement().executeQuery("SELECT * FROM departments");
														while (rsDepartments.next()) {
											%>
											<option value="<%=rsDepartments.getString("abbreviation")%>"
												<%=(rsDepartments.getString("abbreviation")
								.equals(rsExistingCourses
										.getString("department_abbreviation")) ? "selected"
								: "")%>><%=rsDepartments.getString("abbreviation")%></option>
											<%
												}
											%>
									</select></td>
									<td><input
										value="<%=rsExistingCourses.getString("course_number")%>"
										id="coursenumber<%=rsExistingCourses.getInt("course_id")%>"
										name="coursenumber<%=rsExistingCourses.getInt("course_id")%>"
										size="10"></td>
									<%
										rsExistingCourses.getInt("min_units");
									%>
									<td><input
										value="<%=(rsExistingCourses.wasNull() ? ""
							: rsExistingCourses.getInt("min_units"))%>"
										id="minunits<%=rsExistingCourses.getInt("course_id")%>"
										name="minunits<%=rsExistingCourses.getInt("course_id")%>"
										size="10"></td>
									<td><input
										value="<%=rsExistingCourses.getInt("max_units")%>"
										id="maxunits<%=rsExistingCourses.getInt("course_id")%>"
										name="maxunits<%=rsExistingCourses.getInt("course_id")%>"
										size="15"></td>
									<td><select
										id="gradetype<%=rsExistingCourses.getInt("course_id")%>"
										name="gradetype<%=rsExistingCourses.getInt("course_id")%>">
											<option value="gr"
												<%=(rsExistingCourses.getString("grade_type")
							.equals("gr") ? "selected" : "")%>>Graded</option>
											<option value="gs"
												<%=(rsExistingCourses.getString("grade_type")
							.equals("gs") ? "selected" : "")%>>Graded
												or Satisfactory/Unsatisfactory</option>
											<option value="su"
												<%=(rsExistingCourses.getString("grade_type")
							.equals("su") ? "selected" : "")%>>Satisfactory/Unsatisfactory</option>
									</select></td>
									<td><input type="checkbox"
										id="requireslab<%=rsExistingCourses.getInt("course_id")%>"
										name="requireslab<%=rsExistingCourses.getInt("course_id")%>"
										value="required"
										<%=(rsExistingCourses.getBoolean("requires_lab") ? "checked"
							: "")%>>Requires
										Lab</td>
									<td><input type="submit" value="Update"
										onclick="Update(<%=rsExistingCourses.getInt("course_id")%>)"></input>
										<input type="submit" value="Delete"
										onclick="Delete(<%=rsExistingCourses.getInt("course_id")%>)"></input>
									</td>
								</tr>
								<%
									}
								%>
							</table>


						</tr>
					</table>
					</form>
					<%
						} catch (SQLException ex) {
							out.print("SQLException: " + ex.getMessage() + "<br/>");
							out.print("SQLState: " + ex.getSQLState() + "<br/>");
							out.print("VendorError: " + ex.getErrorCode() + "<br/>");
						}
					%> <%
 	
 %>
				</td>
			</tr>
		</table>
</body>
</html>